package com.lb.jwitter.connectDatabase;

import java.sql.*;
import java.util.ArrayList;


public class ConnectTTGD {
	private  String connectionString;
	private  String userName;
	private  String password;
	private  Connection connection;
	private  Statement st;
	public  Connection getConnection(){
		userName="ttgd_remote";
		password="ttgdr3m0t3";
		String databaseName="ttgd_db";
		connection=null;
		connectionString="jdbc:mysql://172.28.2.7:3306/"+databaseName+"?characterEncoding=utf-8";
		try{
			Class.forName("com.mysql.jdbc.Driver");
			connection=DriverManager.getConnection(connectionString, userName, password);
			st=connection.createStatement();
			
		}
		catch(Exception e){
			System.out.println(e);
		}
		return connection;
	}
	public String getJSonData(String query){
		String jsonData="";
		ResultSet data=getData(query);
		ResultSetMetaData rsmd;
		ArrayList<String> listColumnName=new ArrayList<String>();
		try {
			rsmd = data.getMetaData();
			int columnCount = rsmd.getColumnCount();
			for (int i = 1; i < columnCount + 1; i++ ) {
			  String name = rsmd.getColumnName(i);
			  listColumnName.add(name);
			}
		} catch (SQLException e1) {
		
			e1.printStackTrace();
		}
		
		if(data!=null){
			jsonData="[";
			try {
				while (data.next()){
					jsonData=jsonData+"{";
					for(int index=0;index<listColumnName.size();index++){
						String columnName=listColumnName.get(index);
						jsonData=jsonData+"\""+columnName+"\":\""+data.getString(columnName)+"\",";
					}
					jsonData=jsonData+"},";
				}
				jsonData=jsonData.replaceAll(",}", "}");
				jsonData=jsonData+"]";
				jsonData=jsonData.replaceAll(",]", "]");
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
		}
		return jsonData;
	}
	
	public  ResultSet getData(String query){
		ResultSet data=null;
		try {
			data=st.executeQuery(query);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return data;
	}
	public void closeConnection(){
		
		if(connection!=null){
			try {
				st.close();
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
	}
	
}
